/**
 * Copyright (c) 2002 qc&Bull Co.,LTD
 * All right reserved.
 */
package cn.zxd.util;

import org.apache.log4j.Logger;

import java.util.ArrayList;
import java.util.List;

/**
 * 根据Schema对象，拼接相关的sql脚本
 * @author bull
 * @version 1.0
 * @modify 2013-1-11 下午3:07:14
 * @depict 人生五十載，去事恍如夢幻，普天之下，豈有長生不滅者！
 */
public class SQLString
{
	private static Logger logger = Logger.getLogger(SQLString.class);

	/**
	 * 构造函数
	 * @param t 表名
	 */
	public SQLString(String t)
	{
		TableName = t;
	}

	// @Field
	private String TableName;

	private StringBuffer WherePart = new StringBuffer(256);

	private StringBuffer PKWherePart = new StringBuffer(256);

	private StringBuffer UpdPart = new StringBuffer(256);

	private StringBuffer InsPart = new StringBuffer(256);

	private StringBuffer mSql = new StringBuffer(256);

	private List mBV = new ArrayList();

	private static final String mark = "'";

	private static final String paramMark = "?";

	/**
	 * 根据操作类型和Schema对象，生成相应的sql脚本<br>
	 * 脚本通过Statement执行
	 * @param flag 操作类型
	 * @param s Schema对象
	 */
	public void setSQL(int flag, Schema s)
	{
		switch (flag)
		{
			case 1:
				// insert
				this.setInsPart(s);
				if (InsPart.equals(""))
				{
					mSql.setLength(0);
				}
				else
				{
					mSql.append("insert into ");
					mSql.append(TableName);
					mSql.append(" ");
					mSql.append(InsPart);
				}
				break;
			case 2:
				// update (by Primary Key)
				this.setUpdPart(s);
				this.setPKWherePart(s);
				if (UpdPart.equals(""))
				{
					mSql.setLength(0);
				}
				else
				{
					mSql.append("update ");
					mSql.append(TableName);
					mSql.append(" ");
					mSql.append(UpdPart);
					mSql.append(" ");
					mSql.append(PKWherePart);
				}
				break;
			case 3:
				// delete
				this.setWherePart(s);
				mSql.append("delete from ");
				mSql.append(TableName);
				mSql.append(" ");
				mSql.append(WherePart);
				break;
			case 4:
				// delete (by Primary Key)
				this.setPKWherePart(s);
				mSql.append("delete from ");
				mSql.append(TableName);
				mSql.append(" ");
				mSql.append(PKWherePart);
				break;
			case 5:
				// select
				this.setWherePart(s);
				mSql.append("select * from ");
				mSql.append(TableName);
				mSql.append(" ");
				mSql.append(WherePart);
				break;
			case 6:
				// select (by Primary Key)
				this.setPKWherePart(s);
				mSql.append("select * from ");
				mSql.append(TableName);
				mSql.append(" ");
				mSql.append(PKWherePart);
				break;
			case 7:
				// select Count
				this.setWherePart(s);
				mSql.append("select count(1) from ");
				mSql.append(TableName);
				mSql.append(" ");
				mSql.append(WherePart);
				break;
		}
	}

	/**
	 * 根据操作类型和Schema对象，生成相应的sql脚本【国际版】<br>
	 * 脚本通过PreparedStatement执行
	 * @param flag 操作类型
	 * @param s Schema对象
	 */
	public void setSQLNew(int flag, Schema s)
	{
		switch (flag)
		{
			case 1:
			case 2:
			case 3:
			case 4:
			case 6:
				setSQL(flag,s);
			case 5:
				// select
				this.setWherePartNew(s);
				mSql.append("select * from ");
				mSql.append(TableName);
				mSql.append(" ");
				mSql.append(WherePart);
				break;
			case 7:
				// select Count
				this.setWherePartNew(s);
				mSql.append("select count(1) from ");
				mSql.append(TableName);
				mSql.append(" ");
				mSql.append(WherePart);
				break;
		}
	}

	/**
	 * 获取生成的sql脚本
	 * @return sql脚本
	 */
	public String getSQL()
	{
		logger.debug("mSql: " + mSql.toString());
		return mSql.toString();
	}

	/**
	 * 获取生成的sql脚本的绑定变量集合
	 * @return 绑定变量集合
	 */
	public List getBV()
	{
		return this.mBV;
	}

	/**
	 * 根据Schema对象的主键信息，拼接sql脚本的where条件
	 * @param s Schema对象
	 */
	private void setPKWherePart(Schema s)
	{
		PKWherePart.append("where");

		String[] pk = s.getPK();
		int n = pk.length;

		String strFieldName = "";
		StringBuffer strFieldValue = null;
		for (int i = 0; i < n; i++)
		{
			strFieldName = pk[i];
			strFieldValue = new StringBuffer(100);

			int nFieldType = s.getFieldType(strFieldName);
			switch (nFieldType)
			{
				case Schema.TYPE_STRING:
				case Schema.TYPE_DATE:
					// strFieldValue = mark + strFieldValue + mark;
					strFieldValue.append(mark);
					strFieldValue.append(s.getV(strFieldName));
					strFieldValue.append(mark);
					break;
				case Schema.TYPE_DOUBLE:
				case Schema.TYPE_FLOAT:
				case Schema.TYPE_INT:
					strFieldValue.append(s.getV(strFieldName));
					break;
				default:
					System.out.println("出现异常数据类型");
					break;
			}

			if (i != 0)
			{
				PKWherePart.append(" and");
			}

			PKWherePart.append(" ");
			PKWherePart.append(strFieldName);
			PKWherePart.append("=");
			PKWherePart.append(strFieldValue);
		}
	}

	/**
	 * 获取主键where条件
	 * @return 主键where条件
	 */
	public String getPKWherePart()
	{
		return PKWherePart.toString();
	}

	/**
	 * 根据Schema对象的信息，拼接sql脚本的绑定变量信息
	 * @param s Schema对象
	 */
	private void setWherePartNew(Schema s)
	{
		// WherePart = "where";
		WherePart.append("where");

		int nFieldCount = s.getFieldCount();
		int j = 0;
		// 准备绑定 变量的参数
		String strFieldName = "";
		StringBuffer strFieldValue = null;
		for (int i = 0; i < nFieldCount; i++)
		{
			strFieldName = s.getFieldName(i);
			// String strFieldValue = s.getV(i);
			strFieldValue = new StringBuffer(100);

			int nFieldType = s.getFieldType(i);
			boolean bFlag = false;

			String[] tParams = new String[2];
			switch (nFieldType)
			{
				case Schema.TYPE_STRING:
				case Schema.TYPE_DATE:
					// if (!strFieldValue.equals("null"))
					// {
					// strFieldValue = mark + strFieldValue + mark;
					// bFlag = true;
					// }
					if (s.getV(i).equals("null"))
					{
						// 为空就不准备了
						// strFieldValue.append(s.getV(i));
					}
					else
					{
						// strFieldValue.append(mark);
						// strFieldValue.append(s.getV(i));
						// strFieldValue.append(mark);
						strFieldValue.append(paramMark);
						tParams[0] = String.valueOf(nFieldType);
						tParams[1] = s.getV(i);

						bFlag = true;
					}
					break;

				case Schema.TYPE_DOUBLE:
					// if (!strFieldValue.equals("0.0"))
					if (!s.getV(i).equals("0.0"))
					{
						// strFieldValue.append(s.getV(i));
						strFieldValue.append(paramMark);
						tParams[0] = String.valueOf(nFieldType);
						tParams[1] = s.getV(i);

						bFlag = true;
					}
					break;

				case Schema.TYPE_FLOAT:
					// if (!strFieldValue.equals("0.0"))
					if (!s.getV(i).equals("0.0"))
					{
						// strFieldValue.append(s.getV(i));
						strFieldValue.append(paramMark);
						tParams[0] = String.valueOf(nFieldType);
						tParams[1] = s.getV(i);

						bFlag = true;
					}
					break;

				case Schema.TYPE_INT:
					// if (!strFieldValue.equals("0"))
					if (!s.getV(i).equals("0"))
					{
						// strFieldValue.append(s.getV(i));
						strFieldValue.append(paramMark);
						tParams[0] = String.valueOf(nFieldType);
						tParams[1] = s.getV(i);

						bFlag = true;
					}
					break;

				default:
					System.out.println("出现异常数据类型");
					bFlag = false;
					break;
			}

			if (bFlag)
			{
				j++;
				// String wp = "";
				if (j != 1)
				{
					// wp += " and";
					WherePart.append(" and");
				}
				// wp += " " + strFieldName + "=" + strFieldValue;
				// WherePart += wp;
				WherePart.append(" ");
				WherePart.append(strFieldName);
				WherePart.append("=");
				WherePart.append(strFieldValue);

				this.mBV.add(tParams);
			}
		}
		if (j == 0)
		{
			// WherePart = "";
			WherePart.setLength(0);
			throw new IllegalArgumentException("Table " + TableName + " is querying for all data!");
		}
		// System.out.println("the size of params :" + this.mBV.size());
	}

	/**
	 * 根据Schema对象的信息，拼接sql脚本的where条件
	 * @param s Schema对象
	 */
	private void setWherePart(Schema s)
	{
		// WherePart = "where";
		WherePart.append("where");

		int nFieldCount = s.getFieldCount();
		int j = 0;

		String strFieldName = "";
		StringBuffer strFieldValue = null;
		for (int i = 0; i < nFieldCount; i++)
		{
			strFieldName = s.getFieldName(i);
			// String strFieldValue = s.getV(i);
			strFieldValue = new StringBuffer(100);

			int nFieldType = s.getFieldType(i);
			boolean bFlag = false;

			switch (nFieldType)
			{
				case Schema.TYPE_STRING:
				case Schema.TYPE_DATE:
					if (s.getV(i).equals("null"))
					{
						strFieldValue.append(s.getV(i));

					}
					else
					{
						strFieldValue.append(mark);
						strFieldValue.append(s.getV(i));
						strFieldValue.append(mark);

						bFlag = true;
					}
					break;

				case Schema.TYPE_DOUBLE:
					if (!s.getV(i).equals("0.0"))
					{
						strFieldValue.append(s.getV(i));

						bFlag = true;
					}
					break;

				case Schema.TYPE_FLOAT:
					if (!s.getV(i).equals("0.0"))
					{
						strFieldValue.append(s.getV(i));

						bFlag = true;
					}
					break;

				case Schema.TYPE_INT:
					if (!s.getV(i).equals("0"))
					{
						strFieldValue.append(s.getV(i));

						bFlag = true;
					}
					break;

				default:
					System.out.println("出现异常数据类型");
					bFlag = false;
					break;
			}

			if (bFlag)
			{
				j++;
				if (j != 1)
				{
					WherePart.append(" and");
				}
				WherePart.append(" ");
				WherePart.append(strFieldName);
				WherePart.append("=");
				WherePart.append(strFieldValue);

			}
		}
		if (j == 0)
		{
			WherePart.setLength(0);
			throw new IllegalArgumentException("Table " + TableName + " is querying for all data!");
		}

	}

	/**
	 * 获取where条件
	 * @return where条件
	 */
	public String getWherePart()
	{
		return WherePart.toString();
	}

	/**
	 * 根据Schema对象的信息，拼接update脚本的set部分信息
	 * @param s Schema对象
	 */
	private void setUpdPart(Schema s)
	{
		UpdPart.append("set ");

		int nFieldCount = s.getFieldCount();

		String strFieldName = "";
		StringBuffer strFieldValue = null;
		for (int i = 0; i < nFieldCount; i++)
		{
			strFieldName = s.getFieldName(i);
			// String strFieldValue = s.getV(i);
			strFieldValue = new StringBuffer(100);

			int nFieldType = s.getFieldType(i);

			switch (nFieldType)
			{
				case Schema.TYPE_STRING:
				case Schema.TYPE_DATE:

					if (s.getV(i).equals("null"))
					{
						strFieldValue.append(s.getV(i));
					}
					else
					{
						strFieldValue.append(mark);
						strFieldValue.append(s.getV(i));
						strFieldValue.append(mark);
					}
					break;
				case Schema.TYPE_DOUBLE:
				case Schema.TYPE_FLOAT:
				case Schema.TYPE_INT:
					strFieldValue.append(s.getV(i));
					// 不管初始值是0，还是输入0，一律插入数据库。
					break;
				default:
					System.out.println("出现异常数据类型");
					break;
			}

			if (i != 0)
			{
				UpdPart.append(",");
			}
			UpdPart.append(strFieldName);
			UpdPart.append("=");
			UpdPart.append(strFieldValue);
		}
	}

	/**
	 * 获取update脚本的set信息
	 * @return set信息
	 */
	public String getUpdPart()
	{
		return UpdPart.toString();
	}

	/**
	 * 根据Schema对象的信息，拼接insert语句信息
	 * @param s Schema对象
	 */
	private void setInsPart(Schema s)
	{
		StringBuffer ColPart = new StringBuffer(256);
		ColPart.append("( ");
		StringBuffer ValPart = new StringBuffer(256);
		ValPart.append("values ( ");

		int nFieldCount = s.getFieldCount();
		int j = 0;

		String strFieldName = "";
		StringBuffer strFieldValue = null;
		for (int i = 0; i < nFieldCount; i++)
		{
			strFieldName = s.getFieldName(i);
			strFieldValue = new StringBuffer(100);

			int nFieldType = s.getFieldType(i);
			boolean bFlag = false;
			switch (nFieldType)
			{
				case Schema.TYPE_STRING:
				case Schema.TYPE_DATE:
					if (s.getV(i).equals("null"))
					{
						strFieldValue.append(s.getV(i));
					}
					else
					{
						strFieldValue.append(mark);
						strFieldValue.append(s.getV(i));
						strFieldValue.append(mark);
						bFlag = true;
					}
					break;
				case Schema.TYPE_DOUBLE:
				case Schema.TYPE_FLOAT:
				case Schema.TYPE_INT:
					strFieldValue.append(s.getV(i));
					bFlag = true;
					break;
				default:
					bFlag = false; // 不支持的数据类型
					break;
			}

			if (bFlag)
			{
				j++;
				if (j != 1)
				{
					ColPart.append(",");
					ValPart.append(",");
				}
				ColPart.append(strFieldName);
				ValPart.append(strFieldValue);
			}
		}
		ColPart.append(" )");
		ValPart.append(" )");

		InsPart.append(ColPart);
		InsPart.append(" ");
		InsPart.append(ValPart);
		if (j == 0)
		{
			InsPart.setLength(0);
		}
	}

	/**
	 * 获取insert信息
	 * @return insert信息
	 */
	public String getInsPart()
	{
		return InsPart.toString();
	}

	/**
	 * 根据传入的Schema信息，输出真实的insert、delete(主键)、update(主键)执行语句
	 * @param cSchema Schema对象
	 * @param cOperation 操作类型
	 */
	public void printTrueSQL(Schema cSchema, String cOperation)
	{
		StringBuffer tSBSql = new StringBuffer(128);
		if (cOperation.equals("insert"))
		{
			int tFieldCount = cSchema.getFieldCount();
			tSBSql.append("insert into ");
			tSBSql.append(TableName);
			tSBSql.append(" VALUES (");

			for (int i = 0; i < tFieldCount; i++)
			{
				// 得到对应列的值
				String tFieldValue = cSchema.getV(i);
				// 得到对应列的数据类型
				int tFieldType = cSchema.getFieldType(i);
				switch (tFieldType)
				{
					case Schema.TYPE_STRING:
					case Schema.TYPE_DATE:
						if (tFieldValue.equals("null"))
						{
							tSBSql.append("null,");
						}
						else
						{
							tSBSql.append("'");
							tSBSql.append(tFieldValue);
							tSBSql.append("',");
						}
						break;
					case Schema.TYPE_DOUBLE:
					case Schema.TYPE_FLOAT:
					case Schema.TYPE_INT:
						if (tFieldValue == null)
						{
							tSBSql.append("0,");
						}
						else
						{
							tSBSql.append(tFieldValue);
							tSBSql.append(",");
						}
						break;
					default:
						break;
				}

			}
			// 去除最后一列的,
			tSBSql.delete(tSBSql.length() - 1, tSBSql.length());
			tSBSql.append(")");
			System.out.println("True insert sql is ：" + tSBSql.toString());
		}
		else if (cOperation.equals("update"))
		{
			int tFieldCount = cSchema.getFieldCount();
			tSBSql.append("update ");
			tSBSql.append(TableName);
			tSBSql.append(" set ");

			for (int i = 0; i < tFieldCount; i++)
			{
				// 得到对应列的名称
				String tFieldName = cSchema.getFieldName(i);
				// 得到对应列的值
				String tFieldValue = cSchema.getV(i);
				// 得到对应列的数据类型
				int tFieldType = cSchema.getFieldType(i);
				switch (tFieldType)
				{
					case Schema.TYPE_STRING:
					case Schema.TYPE_DATE:
						tSBSql.append(tFieldName);
						tSBSql.append("=");
						if (tFieldValue.equals("null"))
						{
							tSBSql.append("null,");
						}
						else
						{
							tSBSql.append("'");
							tSBSql.append(tFieldValue);
							tSBSql.append("',");
						}
						break;
					case Schema.TYPE_DOUBLE:
					case Schema.TYPE_FLOAT:
					case Schema.TYPE_INT:
						tSBSql.append(tFieldName);
						tSBSql.append("=");
						if (tFieldValue == null)
						{
							tSBSql.append("0,");
						}
						else
						{
							tSBSql.append(tFieldValue);
							tSBSql.append(",");
						}
						break;
					default:
						break;
				}

			}
			// 去除最后一列的,
			tSBSql.delete(tSBSql.length() - 1, tSBSql.length());

			tSBSql.append(" where ");
			// 获得表的主键信息
			String[] tPK = cSchema.getPK();
			int tPKCount = tPK.length;
			for (int i = 0; i < tPKCount; i++)
			{
				// 获得主键的名称
				String tFieldName = tPK[i];
				// 获得主键的值
				String tFieldValue = cSchema.getV(tFieldName);
				// 获得主键的数据类型
				int tFieldType = cSchema.getFieldType(tFieldName);

				switch (tFieldType)
				{
					case Schema.TYPE_STRING:
					case Schema.TYPE_DATE:
						tSBSql.append(tFieldName);
						tSBSql.append("=");
						if (tFieldValue.equals("null"))
						{
							tSBSql.append("null,");
						}
						else
						{
							tSBSql.append("'");
							tSBSql.append(tFieldValue);
							tSBSql.append("',");
						}
						break;
					case Schema.TYPE_DOUBLE:
					case Schema.TYPE_FLOAT:
					case Schema.TYPE_INT:
						tSBSql.append(tFieldName);
						tSBSql.append("=");
						if (tFieldValue == null)
						{
							tSBSql.append("0,");
						}
						else
						{
							tSBSql.append(tFieldValue);
							tSBSql.append(",");
						}
						break;
					default:
						break;
				}
			}
			// 去除最后一列的,
			tSBSql.delete(tSBSql.length() - 1, tSBSql.length());

			System.out.println("True update sql is ：" + tSBSql.toString());
		}
		else
		{
			tSBSql.append("delete from ");
			tSBSql.append(TableName);
			tSBSql.append(" where ");

			// 获得表的主键信息
			String[] tPK = cSchema.getPK();
			int tPKCount = tPK.length;
			for (int i = 0; i < tPKCount; i++)
			{
				// 获得主键的名称
				String tFieldName = tPK[i];
				// 获得主键的值
				String tFieldValue = cSchema.getV(tFieldName);
				// 获得主键的数据类型
				int tFieldType = cSchema.getFieldType(tFieldName);

				switch (tFieldType)
				{
					case Schema.TYPE_STRING:
					case Schema.TYPE_DATE:
						tSBSql.append(tFieldName);
						tSBSql.append("=");
						if (tFieldValue.equals("null"))
						{
							tSBSql.append("null,");
						}
						else
						{
							tSBSql.append("'");
							tSBSql.append(tFieldValue);
							tSBSql.append("',");
						}
						break;
					case Schema.TYPE_DOUBLE:
					case Schema.TYPE_FLOAT:
					case Schema.TYPE_INT:
						tSBSql.append(tFieldName);
						tSBSql.append("=");
						if (tFieldValue == null)
						{
							tSBSql.append("0,");
						}
						else
						{
							tSBSql.append(tFieldValue);
							tSBSql.append(",");
						}
						break;
					default:
						break;
				}
			}
			// 去除最后一列的,
			tSBSql.delete(tSBSql.length() - 1, tSBSql.length());

			System.out.println("True delete sql is ：" + tSBSql.toString());
		}
	}
}